﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Xml;

namespace Common.OpenXml
{
    /// <summary>
    /// help function to write table to excel worksheet
    /// </summary>
    public class GridHelper
    {
        private DataTable _Data;
        /// <summary></summary>
        public DataTable Data
        {
            get
            {
                return this._Data;
            }
            set
            {
                this._Data = value;
            }
        }

        private readonly SharedStrings m_SharedStr;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sharedStr"></param>
        public GridHelper(DataTable dt, SharedStrings sharedStr)
        {
            this._Data = dt;
            m_SharedStr = sharedStr;
        }

        /// <summary>
        /// Writes the data to the SpreadsheetML sheet.
        /// </summary>
        /// <param name="writer">The writer to write the data to.</param>
        public void WriteTo(XmlWriter writer)
        {
            // data type for each column 
            Dictionary<int, Type> colDataTypes = new Dictionary<int, Type>();
            // write the header row
            WriteStartRow(writer, 1);
            for (int i = 0; i < this._Data.Columns.Count; i++)
            {
                colDataTypes.Add(i, this._Data.Columns[i].DataType);
                WriteStringCell(writer, ExcelRangeHelper.GetColumnName(i + 1), 1, this._Data.Columns[i].ColumnName);
            }
            writer.WriteEndElement();

            int index = 2;

            // write the data for each row
            foreach (DataRow dr in this.Data.Rows)
            {
                // write the row header
                WriteStartRow(writer, index);
                // write the field data for current row
                for (int i = 0; i < this._Data.Columns.Count; i++)
                {
                    if (dr[i] != null && dr[i] != DBNull.Value)
                    {
                        // write each cell data
                        if (FieldDataType.IsNumber(FieldDataType.ToDbType(colDataTypes[i].ToString())))
                            WriteNumberCell(writer, ExcelRangeHelper.GetColumnName(i + 1), index,
                                            decimal.Parse(dr[i].ToString()));
                        else
                            WriteStringCell(writer, ExcelRangeHelper.GetColumnName(i + 1), index,
                                            dr[i].ToString());
                    }
                }
                // write the row end element
                writer.WriteEndElement();

                // increment the row index
                index++;
            }
        }

        /// <summary>
        /// column headers are available, do no fill
        /// </summary>
        /// <param name="writer"></param>
        /// <param name="colHeaderRowNum"></param>
        /// <param name="colPositions"></param>
        public void WriteTo(XmlWriter writer, int colHeaderRowNum, Dictionary<string, int> colPositions)
        {
            Dictionary<int, Type> colDataTypes = new Dictionary<int, Type>();
            for (int i = 0; i < this._Data.Columns.Count; i++)
            {
                colDataTypes.Add(i, this._Data.Columns[i].DataType);
            }
            int rowNum = colHeaderRowNum + 1;
            foreach (DataRow dr in this.Data.Rows)
            {
                WriteStartRow(writer, rowNum);
                for (int i = 0; i < this._Data.Columns.Count; i++)
                {
                    string colName = this._Data.Columns[i].ColumnName;
                    int colPos = colPositions[colName];
                    string sheetColName = ExcelRangeHelper.GetColumnName(colPos);
                    if (dr[i] != null && dr[i] != DBNull.Value)
                    {
                        if (FieldDataType.IsNumber(FieldDataType.ToDbType(colDataTypes[i].ToString())))
                            WriteNumberCell(writer, sheetColName, rowNum, decimal.Parse(dr[i].ToString()));
                        else
                            WriteStringCell(writer, sheetColName, rowNum, dr[i].ToString());
                    }
                }
                writer.WriteEndElement();
                rowNum++;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheetXml"></param>
        /// <param name="headerRowPosition"></param>
        /// <param name="columnPositions"></param>
        /// <param name="keyFieldName"></param>
        /// <param name="keyColPosition"></param>
        public void WriteByKey(XmlDocument sheetXml, int headerRowPosition,
            Dictionary<string, int> columnPositions,
            string keyFieldName, int keyColPosition)
        {
            Dictionary<string, int> rowIdxByKeyValue = new Dictionary<string, int>();
            for (int i = 0; i < this._Data.Rows.Count; i++)
            {
                if (this._Data.Rows[i][keyFieldName] != null)
                {
                    string keyValue = this._Data.Rows[i][keyFieldName].ToString();
                    if (!rowIdxByKeyValue.ContainsKey(keyValue))
                        rowIdxByKeyValue.Add(keyValue, i);
                }
            }
            string keyColName = ExcelRangeHelper.GetColumnName(keyColPosition);
            XmlNodeList rowNodes = sheetXml.SelectNodes("/worksheet/sheetData/row");
            foreach (XmlNode rowNode in rowNodes)
            {
                string rowNum = rowNode.Attributes.GetNamedItem("r").Value;
                XmlNode keyCellNode = rowNode.SelectSingleNode(string.Format("c[@r=\"{0}{1}\"]", keyColName, rowNum));
                if (!string.IsNullOrEmpty(keyCellNode.InnerText))
                {
                    string keyValue = this.ReadCellValue(keyCellNode);
                    if (rowIdxByKeyValue.ContainsKey(keyValue))
                    {
                        DataRow dr = this._Data.Rows[rowIdxByKeyValue[keyValue]];
                        foreach (string colName in columnPositions.Keys)
                        {
                            if (colName != keyFieldName && dr[colName] != null)
                            {
                                string colPos = ExcelRangeHelper.GetColumnName(columnPositions[colName]);
                                XmlNode cellNode = rowNode.SelectSingleNode(string.Format("c[@r=\"{0}{1}\"]", colPos, rowNum));
                                if (cellNode == null)
                                {
                                    cellNode = sheetXml.CreateElement(Prefixes.SpreadsheetML, "c", Namespaces.SpreadsheetML);
                                    rowNode.AppendChild(cellNode);
                                }
                                cellNode.RemoveAll();
                                XmlNode valueNode = sheetXml.CreateElement("v");
                                cellNode.AppendChild(valueNode);
                                valueNode.InnerText = dr[colName].ToString();
                            }
                        }
                    }
                }
            }
        }

        #region private methods
        /// <summary>
        /// 
        /// </summary>
        /// <param name="writer"></param>
        /// <param name="row"></param>
        private void WriteStartRow(XmlWriter writer, int row)
        {
            // write the row open element
            // <x:row r="1">
            writer.WriteStartElement("row");
            writer.WriteAttributeString("r", row.ToString());
        }

        /// <summary>
        /// Writes a string cell to the SpreadsheetML document.  It uses the Shared Strings Table to reduce the size of the document.
        /// </summary>
        /// <param name="writer">The writer to write the cell to.</param>
        /// <param name="column">The column to write the data to.</param>
        /// <param name="row">The column to write the data to.</param>
        /// <param name="text">The text to write to the cell</param>
        private void WriteStringCell(XmlWriter writer, string column, int row, string text)
        {
            // remove special characters 
            text = StringUtil.RemoveNonAsciiCharacters(text);
            // write open cell element
            // <x:c r="A1" t="inlineStr">
            writer.WriteStartElement(Prefixes.SpreadsheetML, "c",
                Namespaces.SpreadsheetML);
            writer.WriteAttributeString("r", column + row.ToString());
            //writer.WriteAttributeString("t", "inlineStr");
            writer.WriteAttributeString("t", "s");

            // write the inline string entry
            //   <x:is>
            //     <x:t>String Value</x:t>
            //   </x:is>
            //            writer.WriteStartElement(Prefixes.SpreadsheetML, "is",
            //                Namespaces.SpreadsheetML);
            //            writer.WriteElementString(Prefixes.SpreadsheetML, "t",
            //                Namespaces.SpreadsheetML, text);
            //            writer.WriteEndElement();

            // lookup the shared string index
            int stringIndex = m_SharedStr.LookupString(text);

            // write the shared string entry
            //   <x:v>a</x:v>
            writer.WriteElementString(Prefixes.SpreadsheetML, "v",
                Namespaces.SpreadsheetML, stringIndex.ToString());

            // close the cell
            // </x:c>
            writer.WriteEndElement();
        }

        /// <summary>
        /// Writes a number cell to the SpreadsheetML document.
        /// </summary>
        /// <param name="writer">The writer to write the cell to.</param>
        /// <param name="column">The column to write the data to.</param>
        /// <param name="row">The column to write the data to.</param>
        /// <param name="value">The number value to write to the cell.</param>
        private void WriteNumberCell(XmlWriter writer, string column, int row, decimal value)
        {
            // write open cell element
            // <x:c r="A1">
            writer.WriteStartElement(Prefixes.SpreadsheetML, "c",
                Namespaces.SpreadsheetML);
            writer.WriteAttributeString("r", column + row.ToString());

            // write the numeric value
            //   <x:v>732.00</x:v>
            writer.WriteElementString(Prefixes.SpreadsheetML, "v",
                Namespaces.SpreadsheetML, value.ToString("#0.00"));

            // close the cell
            // </x:c>
            writer.WriteEndElement();
        }

        /// <summary>
        /// Writes a formula cell to the SpreadsheetML document.
        /// </summary>
        /// <param name="writer">The writer to write the cell to.</param>
        /// <param name="column">The column to write the data to.</param>
        /// <param name="row">The column to write the data to.</param>
        /// <param name="formula">The string formula to enter into the cell.</param>
        private void WriteFormulaCell(XmlWriter writer, string column, int row, string formula)
        {
            // write open cell element
            // <x:c r="A1">
            writer.WriteStartElement(Prefixes.SpreadsheetML, "c",
                Namespaces.SpreadsheetML);
            writer.WriteAttributeString("r", column + row.ToString());

            // write the formula value
            //   <x:f>SUM(D2:F2)</x:f>
            writer.WriteElementString(Prefixes.SpreadsheetML, "f",
                Namespaces.SpreadsheetML, formula);

            // close the cell
            // </x:c>
            writer.WriteEndElement();
        }

        private string ReadCellValue(XmlNode cellNode)
        {
            XmlNode valueNode = cellNode.SelectSingleNode("v");
            string value = valueNode.InnerText;
            if (cellNode.Attributes.GetNamedItem("t") != null && cellNode.Attributes.GetNamedItem("t").Value == "s")
            {
                return m_SharedStr.GetString(int.Parse(value));
            }
            else
                return value;
        }
        #endregion


    }
}
